﻿/*
	Tierless Framework
	Test Database
*/

/*
	Table: Employee
*/

create table Employee
(
	ID varchar(40) not null,
	FromDate datetime not null,
	ToDate datetime null,
	Name varchar(50) not null,
	Description varchar(max) null,
	PrimaryPhoneID varchar(40) null,
	constraint PKEmployee primary key ( ID, FromDate ),
	--constraint TKEmployee temporal key ( ID ),
	constraint CKEmployeeFromDateValid check ( FromDate = Convert(DateTime, Floor(Convert(Float, FromDate))) ),
	constraint CKEmployeeToDateValid check ( ToDate = Convert(DateTime, Floor(Convert(Float, ToDate))) ),
	constraint CKEmployeeIntervalValid check ( ToDate >= FromDate )
)
go

/*
	Table: PhoneType
*/

create table PhoneType
(
	ID varchar(3) not null,
	Description varchar(50) not null,
	constraint PKPhoneType primary key ( ID )
)
go

/*
	Table: EmployeePhone
*/

create table EmployeePhone
(
	ID varchar(40) not null,
	FromDate datetime not null,
	ToDate datetime null,
	EmployeeID varchar(40) not null,
	TypeID varchar(3) not null,
	Phone varchar(20) not null,
	constraint PKEmployeePhone primary key ( ID, FromDate ),
	--constraint TKEmployeePhone temporal key ( ID ),
	constraint CKEmployeePhoneFromDateValid check ( FromDate = Convert(DateTime, Floor(Convert(Float, FromDate))) ),
	constraint CKEmployeePhoneToDateValid check ( ToDate = Convert(DateTime, Floor(Convert(Float, ToDate))) ),
	constraint CKEmployeePhoneIntervalValid check ( ToDate >= FromDate ),
	constraint FKEmployeePhonePhoneType foreign key ( TypeID ) references PhoneType ( ID ),
	--constraint TFKEmployeePhoneEmployee temporal foreign key ( EmployeeID ) references Employee ( ID )
)
go

/*
	Table: Department
*/

create table Department
(
	ID varchar(40) not null,
	FromDate datetime not null,
	ToDate datetime null,
	Description varchar(50) not null,
	ParentID varchar(40) null,
	constraint PKDepartment primary key ( ID, FromDate ),
	--constraint TKDepartment temporal key ( ID ),
	constraint CKDepartmentFromDateValid check ( FromDate = Convert(DateTime, Floor(Convert(Float, FromDate))) ),
	constraint CKDepartmentToDateValid check ( ToDate = Convert(DateTime, Floor(Convert(Float, ToDate))) ),
	constraint CKDepartmentIntervalValid check ( ToDate >= FromDate ),
	--constraint TFKDepartmentParentDepartment temporal foreign key ( ParentID ) references Department ( ID )
)
go

/*
	Table: EmployeeDepartment
*/

create table EmployeeDepartment
(
	EmployeeID varchar(40) not null,
	DepartmentID varchar(40) not null,
	FromDate datetime not null,
	ToDate datetime null,
	constraint FKEmployeeDepartment primary key ( EmployeeID, DepartmentID, FromDate ),
	--constraint TKEmployeeDepartment temporal key ( EmployeeID, DepartmentID ),
	constraint CKEmployeeDepartmentFromDateValid check ( FromDate = Convert(DateTime, Floor(Convert(Float, FromDate))) ),
	constraint CKEmployeeDepartmentToDateValid check ( ToDate = Convert(DateTime, Floor(Convert(Float, ToDate))) ),
	constraint CKEmployeeDepartmentIntervalValid check ( ToDate >= FromDate ),
	--constraint TFKEmployeeDepartmentEmployee temporal foreign key ( EmployeeID ) references Employee ( ID ),
	--constraint TFKEmployeeDepartmentDepartment temporal foreign key ( DepartmentID ) references Department ( ID ),
	--constraint TCEmployeeDepartmentDisjoint temporal constraint <No employee may be assigned to a department that is a sub-department of another department for that same employee>
)
go
